package com.cmfi.dao;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cmfi.domain.Line;

@Mapper
public interface LineDao {
	@Insert("insert into tbl_line (lineId,openTime,closeTime) values(#{lineId},#{openTime},#{closeTime})")
    public int save(Line line);
	
    @Update("update tbl_line set lineId = #{lineId}, openTime = #{openTime}, closeTime = #{closeTime} where id = #{id}")
    public int update(Line line);

    @Select("SELECT vinCode"
    		+ "FROM forklift_plan"
    		+ "WHERE updateTime >= CURDATE() AND updateTime < CURDATE() + INTERVAL 1 DAY"
    		+ "AND isReleased = 1")
	public List<Line> getTodayOutput();
	
    @Select("SELECT vinCode"
    		+ "FROM forklift_plan"
    		+ "WHERE updateTime BETWEEN #{beginningTime} AND #{endTime}"
    		+ "AND isReleased = 1")
	public List<Line> getHistoryOutput(String beginningTime, String endTime);

    @Select("SELECT vinCode"
    		+ "FROM forklift_plan"
    		+ "WHERE updateTime > NOW() - INTERVAL 1 HOUR"
    		+ "AND isReleased = 1")
	public List<Line> getOperatingEfficiency();

    @Select("SELECT lineId,closeTime - openTime AS runningTime"
    		+ "FROM (SELECT lineId,openTime,closeTime,"
    		+ "ROW_NUMBER() OVER (PARTITION BY lineId ORDER BY closeTime DESC) AS rn"
    		+ "FROM the_line) AS subquery"
    		+ "WHERE rn = 1 AND lineId IN (1, 2)")
    public List<HashMap<Object, String>> getTodayRunningTime();

    @Select("SELECT lineId,SUM(closeTime - openTime) AS totalRunningTime"
    		+ "FROM tbl_line"
    		+ "WHERE openTime >= #{beginningTime} AND closeTime <= #{endTime}"
    		+ "GROUP BY lineId")
	public List<HashMap<Object, String>> getTotalRunningTime(String beginningTime, String endTime);
	
}
